using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;
using System.Collections.Generic;
using Pms.Utils;
using Biblioteca.Utils;
using Biblioteca.DataBase;
/*
 * 
 * Autor      : Jose Jornando de Carvalho Junior    
 * Data       : 02/04/2010 11:03:48
 * Descricao  : 
 * Alteracoes : 
 * 
 */

namespace SERIX
{
   public class CValoresOrganizacoes
   {
       private string ANO ;
       private string MES ;
       private string IDT_IND ;
       private string IDT_TPO_MED ;
       private string IDT_LOTACAO ;
       private string SEQ_VALOR ;
       private string IDT_ORG ;
       private string VALOR ;
       private string IDT_DESC ;

	   /// <sumary>
       /// Construtor da classe valores_organizacoes
       /// <sumary>
       public CValoresOrganizacoes()
       {
   
       }

	   /// <sumary>
       /// Construtor da classe Valores_organizacoes
       /// <sumary>
       public CValoresOrganizacoes( string ANO, string MES, string IDT_IND, string IDT_TPO_MED, string IDT_LOTACAO, string SEQ_VALOR, string IDT_ORG, string VALOR, string IDT_DESC )
       {
          this.ANO = ANO;
          this.MES = MES;
          this.IDT_IND = IDT_IND;
          this.IDT_TPO_MED = IDT_TPO_MED;
          this.IDT_LOTACAO = IDT_LOTACAO;
          this.SEQ_VALOR = SEQ_VALOR;
          this.IDT_ORG = IDT_ORG;
          this.VALOR = VALOR;
          this.IDT_DESC = IDT_DESC;
       }
	   
	   #region Propriedades
     	/// <sumary>
        /// Ano da coleta
        /// <sumary>
        public string Ano 
		{
		   get
		   {
		       return this.ANO;
		   }
		   set
		   {
		       this.ANO = value;
		   }
		}
     	/// <sumary>
        /// M?s da coleta
        /// <sumary>
        public string Mes 
		{
		   get
		   {
		       return this.MES;
		   }
		   set
		   {
		       this.MES = value;
		   }
		}
     	/// <sumary>
        /// Identificador do indicador
        /// <sumary>
        public string Idt_ind 
		{
		   get
		   {
		       return this.IDT_IND;
		   }
		   set
		   {
		       this.IDT_IND = value;
		   }
		}
     	/// <sumary>
        /// Identificador do tipo de medida
        /// <sumary>
        public string Idt_tpo_med 
		{
		   get
		   {
		       return this.IDT_TPO_MED;
		   }
		   set
		   {
		       this.IDT_TPO_MED = value;
		   }
		}
     	/// <sumary>
        /// Identificador da lota??o
        /// <sumary>
        public string Idt_lotacao 
		{
		   get
		   {
		       return this.IDT_LOTACAO;
		   }
		   set
		   {
		       this.IDT_LOTACAO = value;
		   }
		}
     	/// <sumary>
        /// 
        /// <sumary>
        public string Seq_valor 
		{
		   get
		   {
		       return this.SEQ_VALOR;
		   }
		   set
		   {
		       this.SEQ_VALOR = value;
		   }
		}
     	/// <sumary>
        /// Identificador da organizacao
        /// <sumary>
        public string Idt_org 
		{
		   get
		   {
		       return this.IDT_ORG;
		   }
		   set
		   {
		       this.IDT_ORG = value;
		   }
		}
     	/// <sumary>
        /// Valor coletado
        /// <sumary>
        public string Valor 
		{
		   get
		   {
		       return this.VALOR;
		   }
		   set
		   {
		       this.VALOR = value;
		   }
		}
     	/// <sumary>
        /// Identificador da descricao da opcao
        /// <sumary>
        public string Idt_desc 
		{
		   get
		   {
		       return this.IDT_DESC;
		   }
		   set
		   {
		       this.IDT_DESC = value;
		   }
		}
	    #endregion Propriedades
	
	    /// <summary>
        /// Inserindo um novo Valores_organizacoes.
        /// </summary>
        public void Inserir(string Login)
        {
		    OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"insert into Serix.SRX_VALORES_ORGANIZACOES
			                  ( ANO, MES, IDT_IND, IDT_TPO_MED, IDT_LOTACAO, SEQ_VALOR, IDT_ORG, VALOR, IDT_DESC )
						   values
						      ( :ANO, :MES, :IDT_IND, :IDT_TPO_MED, :IDT_LOTACAO, :SEQ_VALOR, :IDT_ORG, :VALOR, :IDT_DESC )";
     		
			try
			{	
				command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
				
				command.Parameters.Add(new OracleParameter("ANO",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("MES",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_IND",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_TPO_MED",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_LOTACAO",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("SEQ_VALOR",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_ORG",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("VALOR",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_DESC",OracleType.Number)); 
				
                command.Parameters["ANO"].Value = this.ANO;
                command.Parameters["MES"].Value = this.MES;
                command.Parameters["IDT_IND"].Value = this.IDT_IND;
                command.Parameters["IDT_TPO_MED"].Value = this.IDT_TPO_MED;
                command.Parameters["IDT_LOTACAO"].Value = this.IDT_LOTACAO;
                command.Parameters["SEQ_VALOR"].Value = this.SEQ_VALOR;
                command.Parameters["IDT_ORG"].Value = this.IDT_ORG;
                command.Parameters["VALOR"].Value = this.VALOR;
                command.Parameters["IDT_DESC"].Value = this.IDT_DESC;
				command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
		}
		
	    /// <summary>
        /// Alterando um Valores_organizacoes.
        /// </summary>
        public void Alterar(string Login)
        {
		    OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"update Serix.SRX_VALORES_ORGANIZACOES set 
			                   VALOR = :VALOR, IDT_DESC = :IDT_DESC
						   where
						       ANO = :ANO, MES = :MES, IDT_IND = :IDT_IND, IDT_TPO_MED = :IDT_TPO_MED, IDT_LOTACAO = :IDT_LOTACAO, SEQ_VALOR = :SEQ_VALOR, IDT_ORG = :IDT_ORG ";
     		
			try
			{	
				command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
				
				command.Parameters.Add(new OracleParameter("ANO",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("MES",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_IND",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_TPO_MED",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_LOTACAO",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("SEQ_VALOR",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_ORG",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("VALOR",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_DESC",OracleType.Number)); 
				
                command.Parameters["ANO"].Value = this.ANO;
                command.Parameters["MES"].Value = this.MES;
                command.Parameters["IDT_IND"].Value = this.IDT_IND;
                command.Parameters["IDT_TPO_MED"].Value = this.IDT_TPO_MED;
                command.Parameters["IDT_LOTACAO"].Value = this.IDT_LOTACAO;
                command.Parameters["SEQ_VALOR"].Value = this.SEQ_VALOR;
                command.Parameters["IDT_ORG"].Value = this.IDT_ORG;
                command.Parameters["VALOR"].Value = this.VALOR;
                command.Parameters["IDT_DESC"].Value = this.IDT_DESC;
				command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
		}
		
	     /// <summary>
        /// Exclui um  Valores_organizacoes.
        /// </summary>
        public void Excluir(string Login)
        {
            Tabela tab = new Tabela("SRX_VALORES_ORGANIZACOES", BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login,BiblioPMS.Demo));
            tab.adicionarFiltro(new Coluna("ANO", DbType.Int32, this.ANO));
            tab.adicionarFiltro(new Coluna("MES", DbType.Int32, this.MES));
            tab.adicionarFiltro(new Coluna("IDT_IND", DbType.Int32, this.IDT_IND));
            tab.adicionarFiltro(new Coluna("IDT_TPO_MED", DbType.Int32, this.IDT_TPO_MED));
            tab.adicionarFiltro(new Coluna("IDT_LOTACAO", DbType.Int32, this.IDT_LOTACAO));
            tab.adicionarFiltro(new Coluna("SEQ_VALOR", DbType.Int32, this.SEQ_VALOR));
            tab.adicionarFiltro(new Coluna("IDT_ORG", DbType.Int32, this.IDT_ORG));
            tab.delete();
        }
		
		static public DataTable ObterTodos()
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_VALORES_ORGANIZACOES order by ANO ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection)); 
                return tbl; 
                
            }
            catch (Exception e)
            {
 
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
		
		static public CValoresOrganizacoes PesquisaporCodigo(string ano, string mes, string idt_ind, string idt_tpo_med, string idt_lotacao, string seq_valor, string idt_org, string login)
        {
            CValoresOrganizacoes obj;
            string SQL = @"SELECT *
                            FROM  Serix.SRX_VALORES_ORGANIZACOES 
                            WHERE ANO = :ANO, MES = :MES, IDT_IND = :IDT_IND, IDT_TPO_MED = :IDT_TPO_MED, IDT_LOTACAO = :IDT_LOTACAO, SEQ_VALOR = :SEQ_VALOR, IDT_ORG = :IDT_ORG";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
				qy.AdicionarParametro(":ANO", DbType.String, ano);
				qy.AdicionarParametro(":MES", DbType.String, mes);
				qy.AdicionarParametro(":IDT_IND", DbType.String, idt_ind);
				qy.AdicionarParametro(":IDT_TPO_MED", DbType.String, idt_tpo_med);
				qy.AdicionarParametro(":IDT_LOTACAO", DbType.String, idt_lotacao);
				qy.AdicionarParametro(":SEQ_VALOR", DbType.String, seq_valor);
				qy.AdicionarParametro(":IDT_ORG", DbType.String, idt_org);
               
				qy.Executar();
                obj = new CValoresOrganizacoes( qy.ObterCampo("ANO"), qy.ObterCampo("MES"), qy.ObterCampo("IDT_IND"), qy.ObterCampo("IDT_TPO_MED"), qy.ObterCampo("IDT_LOTACAO"), qy.ObterCampo("SEQ_VALOR"), qy.ObterCampo("IDT_ORG"), qy.ObterCampo("VALOR"), qy.ObterCampo("IDT_DESC") );
            }                                  
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return new CValoresOrganizacoes();
            }
			finally
            {
                qy.FecharConexao();
            }
            return obj;
        }
		
		static public int ObterNovoIdt(string login)
        {
            string SQL = @"SELECT PKG_BIBSERIX.OBTER_ANOMESIDT_INDIDT_TPO_MEDIDT_LOTACAOSEQ_VALORIDT_ORG() IDT From dual ";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
				qy.Executar();
				return Convert.ToInt32(qy.ObterCampo("IDT"));
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return-1;
            }
			finally
            {
                qy.FecharConexao();
            }
        }
		
		static public DataTable ObterPorIDT_IND(string idt_ind)
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_VALORES_ORGANIZACOES  where IDT_IND = :IDT_IND order by ANO ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_IND", OracleType.Number));
                command.Parameters["IDT_IND"].Value = idt_ind;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
		static public DataTable ObterPorIDT_TPO_MED(string idt_tpo_med)
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_VALORES_ORGANIZACOES  where IDT_TPO_MED = :IDT_TPO_MED order by ANO ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_TPO_MED", OracleType.Number));
                command.Parameters["IDT_TPO_MED"].Value = idt_tpo_med;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
		static public DataTable ObterPorIDT_LOTACAO(string idt_lotacao)
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_VALORES_ORGANIZACOES  where IDT_LOTACAO = :IDT_LOTACAO order by ANO ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_LOTACAO", OracleType.Number));
                command.Parameters["IDT_LOTACAO"].Value = idt_lotacao;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
		static public DataTable ObterPorIDT_ORG(string idt_org)
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_VALORES_ORGANIZACOES  where IDT_ORG = :IDT_ORG order by ANO ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_ORG", OracleType.Number));
                command.Parameters["IDT_ORG"].Value = idt_org;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
		static public DataTable ObterPorIDT_DESC(string idt_desc)
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_VALORES_ORGANIZACOES  where IDT_DESC = :IDT_DESC order by ANO ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_DESC", OracleType.Number));
                command.Parameters["IDT_DESC"].Value = idt_desc;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
    }
}	

